package com.yuba.front.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuba.common.mybatis.core.mapper.BaseMapperPlus;
import com.yuba.front.domain.FishSite;
import com.yuba.front.domain.param.FishSiteParam;
import com.yuba.front.domain.vo.FishSiteDetailVo;
import com.yuba.front.domain.vo.FishSiteVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 钓场Mapper接口
 *
 * @author modakai
 * @date 2024-12-11
 */
public interface FishSiteMapper extends BaseMapperPlus<FishSite, FishSiteVo> {

    /**
     * 自定义分页查询
     * @param page 分页条件
     * @param fishSite 查询条件
     * @return 分页结果
     */
    Page<FishSiteVo> customerPageList(@Param("page") Page<FishSiteVo> page, @Param("fishSite") FishSiteParam fishSite);

    @Select("""
                select\s
                    fish_site.user_id,
                    fish_site.fish_site_id, fish_site.name,
                    fish_site.address, fish_site.province,\s
                    fish_site.city, fish_site.latitude,\s
                    fish_site.longitude, fish_site.fee_type,\s
                    fish_site.amount, fish_site.description,\s
                    fish_site.image_url, fish_site.public_flag,
                    fish_site.create_by, fish_site.create_time
                from collect_fish_site
                    join fish_site on collect_fish_site.fish_site_id = fish_site.fish_site_id
                where collect_fish_site.user_id = #{userId}
           \s""")
    Page<FishSiteVo> queryPageListUserCollectFishSite(@Param("page") Page<FishSiteVo> build,@Param("userId") Long userId);

    void deleteByKey(@Param("list") List<Long> ids, @Param("userId") Long userId);

    @Select("""
                select fish_site.fish_site_id, name, description, user_id,
                   fish_site_ratings.avg_rating
                          from fish_site
                          join fish_site_ratings on fish_site.fish_site_id = fish_site_ratings.fish_site_id
                      order by fish_site_ratings.avg_rating desc
    """)
    List<FishSiteVo> selectHot();

    @Select("""
                select distinct province from fish_site
                """)
    List<String> selectProvince();

    @Select("""
                select distinct city from fish_site
                """)
    List<String> selectCity();

    @Select("""
        select fish_site.fish_site_id, fish_site.user_id, fish_site.name, fish_site.address, fish_site.province,\s
            fish_site.city, fish_site.latitude,\s
            fish_site.longitude, fish_site.fee_type,\s
            fish_site.amount, fish_site.description,\s
            fish_site.image_url, fish_site.public_flag,\s
            fish_site.create_by, fish_site.create_time,\s
            fish_site_ratings.avg_rating,                   \s
            fish_site_ratings.rating_count,
            fish_site.create_time
        from fish_site
        left join fish_site_ratings on fish_site.fish_site_id = fish_site_ratings.fish_site_id
        where fish_site.fish_site_id = #{fishSiteId}
""")
    FishSiteDetailVo selectVoDetail(Long fishSiteId);
}
